package com.hongdezhi.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.hongdezhi.domain.*;
import com.hongdezhi.utils.C3P0Utils;


public class EmployeesDao {
	// 添加用户的操作 insert
		public boolean insert(Employee emp) throws SQLException{
			QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
			String sql = "insert into employee_inf(name,cardid,nameid,phone,sex,birthday,race,education,remark,password) VALUE(?,?,?,?,?,?,?,?,?,?)";
			int i = runner.update(sql, new Object[]{emp.getName(),emp.getCardid(),emp.getNameid(),emp.getPhone(),emp.getSex(),emp.getBirthday(),emp.getRace(),emp.getEducation(),emp.getRemark(),emp.getPassword()});
			if(i>0)
				return true;
			return false;
		}
		// 查询所有的User对象 query
		public List findAll() throws SQLException{
			
			/*
			 * 
			 select a.NAME,a.SEX,a.PHONE,a.EMAIL,b.NAME,a.EDUCATION,a.CARD_ID,c.NAME,a.ADDRESS,a.CREATE_DATE 
			 	from employee_inf a,dept_inf b,job_inf c 
			 		where a.DEPT_ID = b.ID and a.JOB_ID = c.ID;
			 */
			QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
			String sql = "select * from employee_inf";
			List<Employee> list = runner.query(sql,new BeanListHandler<Employee>(Employee.class));
			if(list.size()>0)
				return list;
			return null;
		}
		// 根据员工姓名查询数据
		public Employee find(String name) throws SQLException {
				
				/**
				 * 使用登录名进行模糊查询 单查询 模糊查询
				 */
				QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
				String sql = "select * from employee_inf where name like ?";
				Employee user = runner.query(sql, new BeanHandler<Employee>(Employee.class), "%"+name+"%");
				if(user!=null)
					return user;
				
				return null;
		}
		//登录操作
		public String login(String nameid,String password) throws SQLException {
			
			/*
			 * select NAME,PASSWORD from employee_inf where NAME="Brain" and PASSWORD="123456";
			 */
			QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
			String sql = "select remark from employee_inf where nameid=? and password=?";
			Employee list = runner.query(sql, new BeanHandler<Employee>(Employee.class), new Object[]{nameid,password});
			if(list!=null)
				return list.remark;
			
			return null;
		}
		
		// 删除用户 update
		public boolean delete(int id) throws SQLException {
				
			QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
			String sql = "DELETE FROM employee_inf WHERE id=?";
			int i = runner.update(sql, new Object[]{id});
			if(i>0)
				return true;
			return false;
		}
		// 修改数据 update 修改查询到的数据
		public boolean update(Employee emp) throws SQLException {
				
				QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
				String sql = "update employee_inf set name=?,cardid=?,nameid=?,phone=?,sex=?,birthday=?,race=?,education=?,remark=?,password=? where id=?";
				int i = runner.update(sql, new Object[]{emp.getName(),emp.getCardid(),emp.getNameid(),emp.getPhone(),emp.getSex(),emp.getBirthday(),emp.getRace(),emp.getEducation(),emp.getRemark(),emp.getPassword(),emp.getId()});
				if(i>0)
					return true;
				return false;
		}
}
